import xlrd
from xlutils.copy import copy
wb=xlrd.open_workbook('成绩单.xls')
ws1=wb.sheet_by_name('排名表')
ws2=wb.sheet_by_name('获奖统计')
nwb=copy(wb)
nws=nwb.get_sheet('获奖统计')
l1=[]
n=0
for x in range(1,13):
    l1+=ws1.col_values(x)[1:]
for y in ws2.col_values(0)[1:]:
    n+=1
    nws.write(n,1,l1.count(y))
nwb.save('成绩单.xls')